IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='SP_TIMKIEMDEANTHEONHANVIEN')
BEGIN
	DROP PROC SP_TIMKIEMDEANTHEONHANVIEN
END
GO
CREATE PROC SP_TIMKIEMDEANTHEONHANVIEN
	@MANV VARCHAR(10)
AS
BEGIN 
IF EXISTS (SELECT * FROM PHANCONG WHERE MA_NVIEN=@MANV)
	BEGIN
	IF NOT EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='KETQUATIMKIEM')
	BEGIN
		
		CREATE TABLE KETQUATIMKIEM(
		MANV		VARCHAR(10),
		TENNV		NVARCHAR(10),
		MADA		INT,
		TENDA		NVARCHAR(30),
		THOIGIAN	FLOAT )
	
		INSERT INTO KETQUATIMKIEM
		SELECT 
		A.MANV,
		A.TENNV,
		C.MADA,
		C.MADA,
		B.THOIGIAN
		FROM NHANVIEN AS A
		INNER JOIN PHANCONG AS B ON A.MANV=B.MA_NVIEN
		INNER JOIN DEAN AS C ON B.SODA=C.MADA
		WHERE A.MANV=@MANV
	END 

	ELSE
	BEGIN
		DELETE FROM KETQUATIMKIEM WHERE MANV=@MANV
		INSERT INTO KETQUATIMKIEM
		SELECT 
		A.MANV,
		A.TENNV,
		C.MADA,
		C.MADA,
		B.THOIGIAN
		FROM NHANVIEN AS A
		INNER JOIN PHANCONG AS B ON A.MANV=B.MA_NVIEN
		INNER JOIN DEAN AS C ON B.SODA=C.MADA
		WHERE A.MANV=@MANV
	END

	SELECT * FROM KETQUATIMKIEM
	END
ELSE 
	BEGIN
		PRINT 'NHAN VIEN NAY DANG NGOI CHOI'
	END
END

/*
	EXEC SP_TIMKIEMDEANTHEONHANVIEN '987987987'
	SELECT * FROM KETQUATIMKIEM

*/